package com.project.dao.impl;

import com.project.bean.*;
import com.project.dao.IStudentDao;
import org.springframework.stereotype.Repository;

import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
@Repository
public class StudentDaoImpl extends BaseDao implements IStudentDao {
    @Override
    public CutPageBean<StudentBean> findByItem(Integer pageNO, String name, String className, String roomAddress) {
        CutPageBean<StudentBean> cutPageBean = new CutPageBean<>();
        this.setConnection();
        try {
            String sql = "SELECT s.*,c.`c_name`,r.`r_address` FROM t_student s JOIN t_room r ON s.`fk_roomId`=r.`pk_roomId` JOIN t_class c ON s.`fk_classId`=c.`pk_classId` where 1=1 ";
            String dynaSql = "";
            if (name != null && !(name.equals(""))) {
                dynaSql += "AND s.`s_name` LIKE '%" + name + "%' ";
            }

            if (className != null && !(className.equals(""))) {
                dynaSql += "AND c.`c_name`='" + className + "' ";
            }
            if (roomAddress != null && !(roomAddress.equals(""))) {
                dynaSql += "AND r.`r_address` LIKE '%" + roomAddress + "%' ";
            }
            ps = con.prepareStatement(sql + dynaSql + " ORDER BY s.`pk_studentId` limit ?,?");
            ps.setObject(1, (pageNO - 1) * CutPageBean.PAGESIZI);
            ps.setObject(2, CutPageBean.PAGESIZI);
            rs = ps.executeQuery();
            while (rs.next()) {
                StudentBean studentBean = this.getStudentBean();
                studentBean.getRoomBean().setAddress(rs.getString("r_address"));
                studentBean.getClassBean().setName(rs.getString("c_name"));
                cutPageBean.getList().add(studentBean);
            }
            ps = con.prepareStatement("SELECT COUNT(s.`pk_studentId`) num FROM t_student s JOIN t_room r ON s.`fk_roomId`=r.`pk_roomId` JOIN t_class c ON s.`fk_classId`=c.`pk_classId` " + dynaSql);
            rs = ps.executeQuery();
            if (rs.next()) {
                cutPageBean.setCount(rs.getInt("num"));
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            this.close();
        }
        return cutPageBean;
    }


    @Override
    public void del(int id) {
        this.setConnection();
        try {
            ps = con.prepareStatement("delete from t_student where pk_studentId = ? ");
            ps.setObject(1, id);

            ps.executeUpdate();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.close();
        }
    }

    @Override
    public void add(StudentBean student) {
        this.setConnection();
        try {
            ps = con.prepareStatement("INSERT INTO t_student(s_name,s_gender,s_phone,s_imgSrc,s_time,fk_roomId,fk_classId) VALUES(?,?,?,?,?,?,?)");
            ps.setObject(1, student.getName());
            ps.setObject(2, student.getGender());
            ps.setObject(3, student.getPhone());
            ps.setObject(4, student.getImSrc());
            ps.setObject(5, student.getTime());
            ps.setObject(6, student.getRoomBean().getId());
            ps.setObject(7, student.getClassBean().getId());
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.close();
        }


    }

    @Override
    public void update(int studentId, int roomId) {
        this.setConnection();
        try {
            ps = con.prepareStatement("update t_student set fk_roomId = ? where pk_studentId = ? ");
            ps.setObject(1, roomId);
            ps.setObject(2, studentId);

            ps.executeUpdate();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.close();
        }
    }

    @Override
    public StudentBean findById(int id) {
        StudentBean studentBean = null;
        this.setConnection();
        try {
            ps = con.prepareStatement("select r.r_address address,c.c_name className,s.* from t_student s,t_room r,t_class c where s.fk_roomId=r.pk_roomId and s.fk_classId = c.pk_classId and s.pk_studentId = ? ");
            ps.setObject(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {

                studentBean = new StudentBean();
                studentBean.setId(rs.getInt("pk_studentId"));
                studentBean.setName(rs.getString("s_name"));
                studentBean.setGender(rs.getString("s_gender"));
                studentBean.setPhone(rs.getString("s_phone"));
                studentBean.setImSrc(rs.getString("s_imgSrc"));
                studentBean.setTime(LocalDate.parse(rs.getString("s_time")));

                RoomBean roomBean = new RoomBean();
                roomBean.setAddress(rs.getString("address"));
                roomBean.setId(rs.getInt("fk_roomId"));
                studentBean.setRoomBean(roomBean);

                ClassBean classBean = new ClassBean();
                classBean.setName(rs.getString("className"));
                classBean.setId(rs.getInt("fk_classId"));
                studentBean.setClassBean(classBean);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close();
        }
        return studentBean;
    }

    @Override
    public List<StudentBean> findByRoomId(int roomId) {
        List<StudentBean> list = new ArrayList<>();
        this.setConnection();
        try {
            ps = con.prepareStatement("SELECT r.r_address address,c.c_name className,s.* FROM t_student s,t_room r,t_class c WHERE s.fk_roomId=r.pk_roomId AND s.fk_classId = c.pk_classId AND s.fk_roomId=? ");
            ps.setObject(1, roomId);
            rs = ps.executeQuery();

            while (rs.next()) {
                StudentBean studentBean = new StudentBean();
                studentBean.setId(rs.getInt("pk_studentId"));
                studentBean.setName(rs.getString("s_name"));
                studentBean.setPhone(rs.getString("s_phone"));
                studentBean.setGender(rs.getString("s_gender"));
                studentBean.setImSrc(rs.getString("s_imgSrc"));
                studentBean.setTime(LocalDate.parse(rs.getString("s_time")));
                RoomBean roomBean = new RoomBean();
                roomBean.setId(rs.getInt("fk_roomId"));
                roomBean.setAddress(rs.getString("address"));
                studentBean.setRoomBean(roomBean);
                ClassBean classBean = new ClassBean();
                classBean.setId(rs.getInt("fk_classId"));
                classBean.setName(rs.getString("className"));
                studentBean.setClassBean(classBean);
                list.add(studentBean);
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.close();
        }

        return list;
    }

    @Override
    public int countByRoom(int roomId) {
        this.setConnection();
        int c = 0;
        try {
            ps = con.prepareStatement("SELECT COUNT(*) c FROM t_room r, t_student s WHERE s.fk_roomId=r.pk_roomId AND r.pk_roomId = ? ");
            ps.setObject(1, roomId);
            rs = ps.executeQuery();

            if (rs.next()) {
                c = rs.getInt("c");
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.close();
        }

        return c;
    }

    @Override
    public int countByClass(int classId) {
        this.setConnection();
        int c = 0;
        try {
            ps = con.prepareStatement("SELECT COUNT(*) c FROM t_class c, t_student s WHERE s.fk_classId=c.pk_classId AND c.pk_classId =?");
            ps.setObject(1, classId);
            rs = ps.executeQuery();
            if (rs.next()) {
                c = rs.getInt("c");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.close();
        }

        return c;
    }

    private StudentBean getStudentBean() throws SQLException {
        StudentBean stu = new StudentBean();
        stu.setId(rs.getInt("pk_studentId"));
        stu.setName(rs.getString("s_name"));
        stu.setGender(rs.getString("s_gender"));
        stu.setPhone(rs.getString("s_phone"));
        stu.setTime(LocalDate.parse(rs.getString("s_time")));
        stu.setImSrc(rs.getString("s_imgSrc"));
        RoomBean room = new RoomBean();
        room.setId(rs.getInt("fk_roomId"));
        stu.setRoomBean(room);
        ClassBean classBean = new ClassBean();
        classBean.setId(rs.getInt("fk_classId"));
        stu.setClassBean(classBean);
        return stu;
    }
}
